﻿Create database QuanLyHocSinh
Go
use QuanLyHocSinh
go

CREATE TABLE Khoi (
    MaKhoi VARCHAR (10) NOT NULL	PRIMARY KEY
);

CREATE TABLE Lop (
    MaLop  VARCHAR (10) NOT NULL	PRIMARY KEY,
    MaKhoi VARCHAR (10) NULL,
	FOREIGN KEY (MaKhoi) REFERENCES Khoi (MaKhoi)
);

CREATE TABLE HocSinh (
    MaHS     VARCHAR (10)   NOT NULL	PRIMARY KEY,
    HoTen    NVARCHAR (50)  NOT NULL,
    GioiTinh NVARCHAR (5)   NOT NULL,
    NgaySinh DATE           NULL,
    DiaChi   NVARCHAR (200) NULL,
    MaLop    VARCHAR (10)   NULL,
    Email    VARCHAR (100)  NULL,
    SDT      VARCHAR (15)   NULL,
	FOREIGN KEY (MaLop) REFERENCES Lop (MaLop) ON UPDATE CASCADE
);

CREATE TABLE Mon (
    MaMon  VARCHAR (10)  NOT NULL	PRIMARY KEY,
    TenMon NVARCHAR (20) NULL
);

CREATE TABLE Diem (
    MaHS VARCHAR (10) NOT NULL	PRIMARY KEY,
    HK1  FLOAT   NULL,
    HK2  FLOAT   NULL,
);

CREATE TABLE CTDiem (
    MaHS   VARCHAR (10) NOT NULL,
    MaMon  VARCHAR (10) NOT NULL,
    MaHK   VARCHAR (5)  NOT NULL,
    D15P   FLOAT   NULL,
    D1Tiet FLOAT   NULL,
    DHK    FLOAT   NULL,
    DTB    FLOAT   NULL,
	PRIMARY KEY (MaHS, MaMon, MaHK),
	FOREIGN KEY (MaHS) REFERENCES HocSinh (MaHS) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (MaMon) REFERENCES Mon (MaMon) ON DELETE CASCADE ON UPDATE CASCADE,
);

CREATE TABLE QuyDinh (
    MaQuyDinh  VARCHAR (10)  NOT NULL	PRIMARY KEY,
    TenQuyDinh NVARCHAR (10) NULL,
    GiaTri     FLOAT    NULL,
    GhiChu     NVARCHAR (50) NULL,
);

CREATE TABLE ThongTin (
    id     INT	NOT NULL PRIMARY KEY,
    Ten    NVARCHAR (50),
    DiaChi NVARCHAR (200),
    DT     VARCHAR (15),
    Logo   NVARCHAR (300),
);

CREATE TABLE Account (
    Username NVARCHAR (50) NOT NULL	PRIMARY KEY,
    Password NVARCHAR (50),
    Quyen    VARCHAR (20),
    MaLop    VARCHAR (10),
    MaMon    VARCHAR (10),
    FOREIGN KEY (MaMon) REFERENCES Mon (MaMon) ON UPDATE CASCADE,
    FOREIGN KEY (MaLop) REFERENCES Lop (MaLop) ON UPDATE CASCADE
);

INSERT INTO Khoi VALUES ('10')
INSERT INTO Khoi VALUES ('11')
INSERT INTO Khoi VALUES ('12')
INSERT INTO Lop VALUES ('10A1', '10')
INSERT INTO Lop VALUES ('10A2', '10')
INSERT INTO Lop VALUES ('10A3', '10')
INSERT INTO Lop VALUES ('10A4', '10')
INSERT INTO Lop VALUES ('11A1', '11')
INSERT INTO Lop VALUES ('11A2', '11')
INSERT INTO Lop VALUES ('11A3', '11')
INSERT INTO Lop VALUES ('12A1', '12')
INSERT INTO Lop VALUES ('12A2', '12')
INSERT INTO Mon VALUES ('D1', N'Ðịa lý')
INSERT INTO Mon VALUES ('D2', N'Đạo đức')
INSERT INTO Mon VALUES ('H1', N'Hóa Học')
INSERT INTO Mon VALUES ('L1', N'Vật Lý')
INSERT INTO Mon VALUES ('S1', N'Sinh Học')
INSERT INTO Mon VALUES ('S2', N'Lịch sử')
INSERT INTO Mon VALUES ('T1', N'Toán')
INSERT INTO Mon VALUES ('T2', N'Thể dục')
INSERT INTO Mon VALUES ('V1', N'Ngữ Văn')
INSERT INTO QuyDinh VALUES ('DiemDat', N'Điểm đạt', 5, N'Điểm số tối thiểu để đạt')
INSERT INTO QuyDinh VALUES ('MaxSiSo', N'Max Sỉ số', 40, N'Sỉ số tối đa của lớp')
INSERT INTO QuyDinh VALUES ('MaxTuoi', N'Max tuổi', 20, N'Tuổi tối đa của học sinh')
INSERT INTO QuyDinh VALUES ('MinTuoi', N'Min tuổi', 15, N'Tuổi tối thiểu của học sinh')
INSERT INTO ThongTin (id, Ten, DiaChi, DT, Logo) VALUES (1, N'Trung học phổ thông', N'12/12 Nguyễn Thị Minh Khai', N'01233456789', N'd:\logo.png')
INSERT INTO Account VALUES (N'admin', N'123456', N'admin', NULL, NULL)
INSERT INTO Account VALUES (N'gv1', N'123456', N'gv', NULL, N'D1')
INSERT INTO Account VALUES (N'gv2', N'123456', N'gv', N'10A1', NULL)


GO
CREATE TRIGGER TGDiem1
    ON CTDiem
    FOR INSERT, UPDATE
    AS BEGIN
           DECLARE @MaHS AS VARCHAR (10);
           DECLARE @MHK1 AS INT;
           DECLARE @MHK2 AS INT;
           DECLARE @TM AS INT;
           SET @MaHS = (SELECT MaHS
                        FROM   inserted);
           SET @TM = (SELECT count(*)
                      FROM   Mon);
           SET @MHK1 = (SELECT count(*)
                        FROM   CTDiem
                        WHERE  MaHS = @MaHS
                               AND MaHK = 'HK1'
                               AND DTB != NULL);
           SET @MHK2 = (SELECT count(*)
                        FROM   CTDiem
                        WHERE  MaHS = @MaHS
                               AND MaHK = 'HK2'
                               AND DTB != NULL);
           IF (@MHK1 = @TM)
               UPDATE Diem
               SET    HK1 = (SELECT sum(DTB)
                             FROM   CTDiem
                             WHERE  MaHS = @MaHS) / @TM
               WHERE  MaHS = @MaHS;
           ELSE
               UPDATE Diem
               SET    HK1 = NULL
               WHERE  MaHS = @MaHS;
           IF (@MHK2 = @TM)
               UPDATE Diem
               SET    HK2 = (SELECT sum(DTB)
                             FROM   CTDiem
                             WHERE  MaHS = @MaHS) / @TM
               WHERE  MaHS = @MaHS;
           ELSE
               UPDATE Diem
               SET    HK2 = NULL
               WHERE  MaHS = @MaHS;
       END

GO
CREATE TRIGGER TGDiem2
    ON CTDiem
    FOR DELETE
    AS BEGIN
           DECLARE @MaHS AS VARCHAR (10);
           DECLARE @MaHK AS VARCHAR (5);
           SET @MaHS = (SELECT MaHS
                        FROM   deleted);
           SET @MaHK = (SELECT MaHK
                        FROM   deleted);
           IF (@MaHK = 'HK1')
               UPDATE Diem
               SET    HK1 = NULL
               WHERE  MaHS = @MaHS;
           ELSE
               UPDATE Diem
               SET    HK2 = NULL
               WHERE  MaHS = @MaHS;
       END